﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using BitBox.Framework.Util;
using BitBox.Framework.KBR.Model;
using BitBox.Framework.KBR.Model.Entity;

namespace BitBox.Framework.KBR.DAL
{
    public class UserDAL
    {
        #region /* Atributos Privados */
        
        private dcKBRDataContext dc;
        
        #endregion



        #region /* Métodos Privados */

        private IQueryable<fnTbUserResult> Select()
        {
            try
            {
                var result = (from User in dc.fnTbUser()
                              select User);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion



        #region /* Métodos Públicos */

        /// <summary>
        /// Seleciona com base no info, filtrando de acordo com os campos preenchidos
        /// </summary>
        /// <param name="info"></param>
        /// <returns>Retorna uma lista</returns>
        public List<UserInfo> SelectByAnyFilters(UserInfo info)
        {
            List<UserInfo> retorno = new List<UserInfo>();

            try
            {
                dc = DALHelper.GetDataContext();

                var result = (from User in Select()
                              select User);
                // Se USER_ID vier preenchido, acrescenta where
                if(!info.USER_ID.Equals(0))
                    result = result.Where(re => re.USER_ID.Equals(info.USER_ID));
                // Se USER_NAME vier preenchido, acrescenta where
                if (!String.IsNullOrEmpty(info.USER_NAME))
                    result = result.Where(re => re.USER_NAME.Contains(info.USER_NAME));

                retorno = LinqHelper<UserInfo, fnTbUserResult>.ListResultToInfo(result.ToList<fnTbUserResult>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dc.Dispose();
            }
            return retorno;
        }

        /// <summary>
        /// Seleciona com base no User_ID
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns>Retorna um elemento Info</returns>
        public UserInfo SelectByCode(int UserId)
        {
            UserInfo retorno = new UserInfo();

            try
            {
                dc = DALHelper.GetDataContext();

                var result = (from User in Select()
                              where User.USER_ID == UserId
                              select User);

                UserInfo info = new UserInfo(result.SingleOrDefault());

                retorno = new UserInfo(info);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dc.Dispose();
            }
            return retorno;
        }

        /// <summary>
        /// Seleciona com base no User_Name
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns>Retorna um elemento Info</returns>
        public UserInfo SelectByName(String UserName)
        {
            UserInfo retorno = new UserInfo();

            try
            {
                dc = DALHelper.GetDataContext();

                var result = (from User in Select()
                              where User.USER_NAME == UserName
                              select User);

                UserInfo info = new UserInfo(result.SingleOrDefault());

                retorno = new UserInfo(info);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dc.Dispose();
            }
            return retorno;
        }

        /// <summary>
        /// Executa um Insert com base nas informações de um elemento Info
        /// </summary>
        /// <param name="info"></param>
        /// <returns>Retorna o Identity</returns>
        public int Insert(UserInfo info)
        {
            int UserID = 0;

            try
            {
                dc = DALHelper.GetDataContext();

                UserID = dc.PR_TB_USER_INSERT
                    (
                        info.USER_NAME,
                        info.USER_PASSWORD
                    );
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dc.Dispose();
            }
            return UserID;
        }

        /// <summary>
        /// Executa um Update com base nas informações de um elemento Info
        /// </summary>
        /// <param name="info"></param>
        public int Update(UserInfo info)
        {
            int UserID = 0;

            try
            {
                dc = DALHelper.GetDataContext();

             UserID = dc.PR_TB_USER_UPDATE
                    (
                        info.USER_ID,
                        info.USER_NAME,
                        info.USER_PASSWORD
                    );
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dc.Dispose();
            }

            return UserID;
        }

        /// <summary>
        /// Executa um Delete com base no User_ID enviado
        /// </summary>
        /// <param name="UserID"></param>
        public void Delete(int UserID)
        {
            try
            {
                dc = DALHelper.GetDataContext();

                dc.PR_TB_USER_DELETE(UserID);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dc.Dispose();
            }
        }

        #endregion
    }
}
